package dbtools;

/**
 * Title: SQL语句操作相关静态方法
 * Desc:
 */

import java.io.File;
import java.io.FileInputStream;
import java.sql.*;
import java.util.ArrayList;

public class DBOperator {
	/**
	 * 取得数据库连接
	 * @param nowConn Connect 连接名称
	 */
	public static final Connection getConnect() {
		try {
			DBConnFactory cfConnFact = new DBConnFactory();
			return cfConnFact.getConnection();
		} catch (Throwable t) {
			t.printStackTrace();
			return null;
		} finally {
		}
	}
	/**
	 * 释放数据库连接
	 * @param nowConn Connect 连接名称
	 */
	public static final void freeConnect(Connection nowConn) {
		try {
			DBConnFactory cfConnFact = new DBConnFactory();
			cfConnFact.freeConnection(nowConn);
		} catch (Throwable t) {
			t.printStackTrace();
		} finally {
		}
	}
	/**
	 * 执行表DML语句操作
	 * @String  DML语句
	 * @Connect 表连接
	 * @Return String  ok/error
	 */
	public static final String execDML(String strSql) {
		Connection con = getConnect();
		if (con == null)
			return "error";
		Statement stmtDB;
		ResultSet rsetTemp;
		int nCode;
		String tableName = "";
		try {
			stmtDB = con.createStatement();
			String strTemp = strSql.toLowerCase().trim();

			if (strTemp.startsWith("create")
				|| strTemp.startsWith("drop")
				|| strTemp.startsWith("alter")
				|| strTemp.startsWith("delete")
				|| strTemp.startsWith("insert")
				|| strTemp.startsWith("update")) {

				tableName = strTemp;
				stmtDB.executeUpdate(strSql);
				stmtDB.close();
				stmtDB = null;
				rsetTemp = null;
				//freeConnect(con);
				con.close();
				con = null;
				return "ok";
			} else {
				System.err.println("not dml format!");
				stmtDB.close();
				stmtDB = null;
				rsetTemp = null;
				//freeConnect(con);
				con.close();
				con = null;
				return "error";
			}
		} catch (SQLException e) {
			e.printStackTrace();
			stmtDB = null;
			rsetTemp = null;
			//freeConnect(con);
			con = null;
			return e.toString();
		} finally {
			stmtDB = null;
			rsetTemp = null;
			con = null;
		}
	}

	/**
	 * 执行数据表 增删改SQL 事务
	 * @param ArrayList  执行操作语句组
	 * @return String  ok--成功   error--未成功
	 */
	public static final String execSql(ArrayList sqls) {
		Statement stmtDB = null;
		if (sqls.size() < 1) {
			return "ok";
		}
		Connection con = getConnect();
		if (con == null)
			return "error";

		try {
			con.setAutoCommit(false);
			stmtDB = con.createStatement();
			String strSql = "";
			int j=0;
			for (int i = 0; i < sqls.size(); i++) {
				strSql = ((String) sqls.get(i)).trim();

				if (strSql.endsWith(";")) {
					strSql = strSql.substring(0, strSql.length() - 1);
				}
				stmtDB.addBatch(strSql);
				j++;
				if(j>98)
				{
					j=0;
					stmtDB.executeBatch();
					stmtDB.clearBatch();
				}
			}
			if(stmtDB.getFetchSize()>0)
			     stmtDB.executeBatch();
			con.commit();

			con.setAutoCommit(true);

			stmtDB.close();
			stmtDB = null;
			con.close();
			//freeConnect(con);
			con = null;
			return "ok";
		} catch (SQLException se) {
			se.printStackTrace();
			try {
				con.rollback();
				con.setAutoCommit(true);
				stmtDB.close();
				con.close();
				//freeConnect(con);
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		}catch (Exception se) {
		se.printStackTrace();
		return String.valueOf(se.toString());
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	/**
	 * 执行数据表 增删改 单句操作
	 *
	 * @param str 执行操作语句
	 * @return-- (String) "ok" 成功 "error" 未成功)
	 *
	 */
	public static final String execSql(String str) {
		Statement stmtDB = null;
		Connection con = getConnect();
		if (con == null)
			return "error";
		try {
			stmtDB = con.createStatement();
			stmtDB.executeUpdate(str);
			stmtDB.close();
			stmtDB = null;
			con.close();
			//freeConnect(con);
			con = null;
			return "ok";
		} catch (Exception se) {
			se.printStackTrace();
			try {
				stmtDB.close();
				con.close();
				//freeConnect(con);
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	/**
	 * 执行数据表 增删改 单句操作 补充
	 *
	 * @param str 执行操作语句
	 * @return-- (String) "zero" 影响0条记录 "ok" 成功 "error" 未成功)
	 *
	 */
	public static final String execSqlComple(String str) {
	 
		Statement stmtDB = null;
		Connection con = getConnect();
		int results = 0;

		if (con == null)
			return "error";
		try {
			stmtDB = con.createStatement();
			results = stmtDB.executeUpdate(str);
			stmtDB.close();
			stmtDB = null;
			//freeConnect(con);
			con.close();
			con = null;
			if (results == 0) {
				return "zero";
			} else {
				return "ok";
			}
		} catch (Exception se) {
			se.printStackTrace();
			try {
				stmtDB.close();
				con.close();
				//freeConnect(con);
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	/**
	 * 执行数据表 增删改 单句操作(带数据库名称)
	 *
	 * @param str 执行操作语句
	 * @return-- (String) "ok" 成功 "error" 未成功)
	 *
	 */
	public static final String execSql(String dbname, String str) {
		str = SqlTools.standexecsql(str, dbname.toUpperCase());
		Statement stmtDB = null;
		Connection con = getConnect();
		if (con == null)
			return "error";
		try {
			stmtDB = con.createStatement();
			stmtDB.executeUpdate(str);
			stmtDB.close();
			stmtDB = null;
			//freeConnect(con);
			con.close();
			con = null;
			return "ok";
		} catch (Exception se) {
			se.printStackTrace();
			try {
				stmtDB.close();
				//freeConnect(con);
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	/**
	 * 执行数据表 增删改SQL 事务
	 * @param ArrayList  执行操作语句组
	 * @return String  ok--成功   error--未成功
	 */
	public static final String execSql(String dbname, ArrayList sqls) {

		Statement stmtDB = null;
		if (sqls.size() < 1) {
			return "ok";
		}
		Connection con = getConnect();
		if (con == null)
			return "error";

		try {
			con.setAutoCommit(false);
			stmtDB = con.createStatement();
			String strSql = "";
			for (int i = 0; i < sqls.size(); i++) {
				strSql = (String) sqls.get(i);
				if (strSql.endsWith(";")) {
					strSql = strSql.substring(0, strSql.length() - 1);
				}
				stmtDB.addBatch(strSql);
			}
			stmtDB.executeBatch();
			con.commit();

			con.setAutoCommit(true);

			stmtDB.close();
			stmtDB = null;
			//freeConnect(con);
			con.close();
			con = null;
			return "ok";
		} catch (SQLException se) {
			se.printStackTrace();
			try {
				con.rollback();
				con.setAutoCommit(true);
				stmtDB.close();
				//freeConnect(con);
				con.close();
				con = null;
				return String.valueOf(se.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			stmtDB = null;
		}
	}

	/**
	 * 构造分页结果集SQL语句
	 * @param  sql         ：select 语句
	 * @param  fromsql     : from   语句
	 * @param  wheresql    : where  语句
	 * @param  orderSql    : ORDER  语句
	 * @param  startIndex  : 起始位置
	 * @param  pageSize    : 每页记录数
	 * @param  groupsql    : GROUP  语句
	 * @param  addsql      : addsql 语句
	 * @return String
	 */
	public static final String initQuerySQL(
		String sql,
		String fromsql,
		String wheresql,
		String orderSql,
		int startIndex,
		int pageSize,
		String groupsql,
		String addsql) {
 
	/*	StringBuffer querySQL = new StringBuffer();
		int totrow = pageSize + startIndex;
		querySQL
			.append("select * from (select my_table.*,rownum as my_rownum from(")
			.append(
				sql
					+ " "
					+ fromsql
					+ " "
					+ wheresql
					+ " "
					+ groupsql
					+ " "
					+ orderSql)
			.append(") my_table ")
			.append(") where my_rownum>")
			.append(startIndex + " and my_rownum<=" + totrow);*/



	/*	return addsql + " " + querySQL.toString();*/

		StringBuffer querySQL = new StringBuffer();
		int totrow = pageSize + startIndex;
		querySQL.append(addsql + " " + sql + " " + fromsql + " " + wheresql
				+ " " + groupsql + " " + orderSql + " limit " + startIndex
				+ "," + pageSize);

		return querySQL.toString();
	}
 
	/**
	 * 执行无返回值存储过程
	 *
	 * @param str 存储过程名称
	 * @param ArrayList 传入参数集合(全部为字符串)
	 * @return-- (String) "ok" 成功 "error" 未成功)
	 *
	 */
	public static final String execProc(String str, ArrayList tmparr) {
		Connection con = getConnect();
		CallableStatement cstmt = null;

		if (con == null)
			return "error";
		try {
			CallableStatement proc = null;
			str = " { call " + str.trim() + "(";
			for (int s = 0; s < tmparr.size(); s++) {
				str += "?,";
			}
			if (str.endsWith(",")) {
				str = str.substring(0, str.length() - 1);
			}
			str += ")}";

			proc = con.prepareCall(str);
			for (int s = 0; s < tmparr.size(); s++) {
				proc.setString(s + 1, (String) tmparr.get(s));
			}
			proc.execute();

			//freeConnect(con);
			con.close();
			con = null;
			return "ok";
		} catch (Exception se) {
			se.printStackTrace();
			try {
				if(cstmt != null)
				   cstmt.close();
				//freeConnect(con);
				con.close();
				con = null;
				return String.valueOf(
					se.toString() + " 调用参数-" + tmparr.toString());
			} catch (SQLException se1) {
				se1.printStackTrace();
				return String.valueOf(se1.toString());
			}
		} finally {
			con = null;
			cstmt = null;
		}
	}
	/**
	 * 执行有返回值存储过程
	 *
	 * @param str 存储过程名称
	 * @param ArrayList 传入参数集合(全部为字符串) 
	 * @param int 几个返回值 (全部为字符串)
	 * @return-- Arraylist  返回值集合
	 *
	 */
	public static final ArrayList execProc(
		String str,
		ArrayList tmparr,
		int nretnum) {
		Connection con = getConnect();
		CallableStatement cstmt = null;

		if (con == null)
			return new ArrayList();
		try {
			ArrayList tmparrret = new ArrayList();
			CallableStatement proc = null;
			str = " { call " + str.trim() + "(";
			for (int s = 0; s < tmparr.size(); s++) {
				str += "?,";
			}
			for (int s = 0; s < nretnum; s++) {
				str += "?,";
			}

			if (str.endsWith(",")) {
				str = str.substring(0, str.length() - 1);
			}
			str += ")}";

			proc = con.prepareCall(str);
			int ntotonum = tmparr.size() + 1;
			for (int s = 0; s < tmparr.size(); s++) {
				proc.setString(s + 1, (String) tmparr.get(s));
			}
			for (int s = 0; s < nretnum; s++) {
				proc.registerOutParameter(s + ntotonum, Types.VARCHAR);
			}
			proc.execute();
			for (int s = 0; s < nretnum; s++) {
				String retvalue = proc.getString(s + ntotonum);
				tmparrret.add(retvalue);
			}

			//freeConnect(con);
			con.close();
			con = null;
			return tmparrret;

		} catch (Exception se) {
			se.printStackTrace();
			try {
				cstmt.close();
				//freeConnect(con);
				con.close();
				con = null;
				return new ArrayList();
			} catch (SQLException se1) {
				se1.printStackTrace();
				return new ArrayList();
			}
		} finally {
			con = null;
			cstmt = null;
		}
	}
	/**
	 * 执行有返回值存储过程
	 *
	 * @param str 存储过程名称
	 * @param ArrayList 参数集合(全部为字符串)
	 * @param int 结果集有几个返回值 (全部为字符串)
	 * @return-- Arraylist  返回值集合(String[])
	 *
	 */
	public static final ArrayList execProcSet(
		String str,
		ArrayList tmparr,
		int nretnum) {
		Connection con = getConnect();
		CallableStatement cstmt = null;
		ResultSet rs = null;

		if (con == null)
			return new ArrayList();
		try {
			ArrayList tmparrret = new ArrayList();
			CallableStatement proc = null;
			str = " { call " + str.trim() + "(";
			for (int s = 0; s < tmparr.size(); s++) {
				str += "?,";
			}
			str += "?";
			str += ")}";

			proc = con.prepareCall(str);
			int ntotonum = tmparr.size() + 1;
			for (int s = 0; s < tmparr.size(); s++) {
				proc.setString(s + 1, (String) tmparr.get(s));
			}
			proc.registerOutParameter(ntotonum, oracle.jdbc.OracleTypes.CURSOR);
			proc.execute();
			rs = (ResultSet) proc.getObject(ntotonum);

			while (rs.next()) {
				String[] tmpstr = new String[nretnum];
				for (int s = 0; s < nretnum; s++) {
					tmpstr[s] = rs.getString(s + 1);
				}
				tmparrret.add(tmpstr);
			}
			if (rs != null) {
				rs.close();
			}
            con.close();
			//freeConnect(con);
			con = null;
			return tmparrret;

		} catch (Exception se) {
			se.printStackTrace();
			try {
				rs.close();
				cstmt.close();
				//freeConnect(con);
				con.close();
				con = null;
				return new ArrayList();
			} catch (SQLException se1) {
				se1.printStackTrace();
				return new ArrayList();
			}
		} finally {
			con = null;
			cstmt = null;
		}
	}
	/**
	 * 将文件以二进制方式写入BLOB字段
	 * @param filename  文件名称- 含路径
	 * @param wheresql  where 条件
	 * @param tablename 数据表名称
	 * @param fieldname 字段名称
	 * @return ok /出错信息
	 */

	public static final String updateBlob(
		String filename,
		String wheresql,
		String tablename,
		String fieldname) {
		String updsql =
			"update "
				+ tablename
				+ " set "
				+ fieldname
				+ "=? where "
				+ wheresql;
		File files = new File(filename);
		long l1 = files.length();
		String ret = "error";
		int l2 = (int) l1;
		try {
			FileInputStream fis = new FileInputStream(files);
			Connection con = getConnect();
			PreparedStatement ps = con.prepareStatement(updsql);

			ps.setBinaryStream(1, fis, l2);
			ps.executeUpdate();
			ps.close();
			fis.close();
			con.close();
			return "ok";
		} catch (Exception e) {
			e.printStackTrace();
		}

		return ret;
	}
}

